/*
 * jxDao Project (2014)
 * 
 * by Denilson Edinaldo Pinto
 * 
 * LGPL
 * Copyright (C) 2014
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package org.jxDao.dao;

import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.jxDao.dao.db.DBInfo;
import org.jxDao.dao.db.Driver;
import org.jxDao.dao.exception.JXDaoException;
import org.jxDao.dao.i18n.I18N;
import org.jxUtils.check.ParameterCheck;

/**
 * @author Denilson Edinaldo Pinto
 * @porject jxDao
 * @package org.jxDao.dao
 * @date 12/04/2014 - 09:27:46
 */
public final class Conn implements Closeable {
	
	private static int openConnections = 0;
	
	private final List<Statement> statements = new ArrayList<>();
	
	private final long hash = System.currentTimeMillis();
	
	private boolean transactionIsOpen = false;
	
	private DBManager manager;
	
	private DBInfo dbInfo = null;
	
	private Connection connection = null;
	
	/**
	 * initConnection is true and autoCommit is true
	 * 
	 * @param dbInfo
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Conn(final DBInfo dbInfo) throws SQLException, ClassNotFoundException {
	
		this(dbInfo, true);
	}
	
	/**
	 * @param dbInfo
	 * @param initConnection
	 *        if it is true then autoCommit is true
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Conn(final DBInfo dbInfo, final boolean initConnection) throws SQLException, ClassNotFoundException {
	
		this(dbInfo, initConnection, true);
	}
	
	/**
	 * @param dbInfo
	 * @param initConnection
	 *        if it is true then autoCommit parameter will be used
	 * @param autoCommit
	 * @throws JXDaoException
	 */
	public Conn(final DBInfo dbInfo, final boolean initConnection, final boolean autoCommit) throws JXDaoException {
	
		ParameterCheck.isNull(dbInfo, "dbInfo");
		
		this.dbInfo = dbInfo;
		
		//
		if(initConnection) {
			
			newConnection(autoCommit);
		}
	}
	
	/**
	 * @return true if connection or transaction is closed
	 * @throws SQLException
	 *         if connection or transaction is open
	 */
	private boolean connectionOrTransactionIsOpen() throws SQLException {
	
		// Se já existe uma Transaction aberta
		if(transactionIsOpen()) {
			
			throw new JXDaoException(I18N.DAO.transactionAlreadyOpen());
		}
		
		// Se já existe uma Connection aberta
		if(isOpen() && isAutoCommit()) {
			
			throw new JXDaoException(I18N.DAO.connectionAlreadyOpen());
		}
		
		return false;
	}
	
	/**
	 * @return statement
	 * @throws JXDaoException
	 */
	private Statement createStatementLocal() throws JXDaoException {
	
		try {
			// Tenta criar o Statement
			return getConnection().createStatement();
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @return driverName
	 */
	private String getDriverName() {
	
		return getDBInfo().getDriver().getName();
	}
	
	/**
	 * Try to start the transaction
	 * 
	 * @throws SQLException
	 */
	public void begin() throws SQLException {
	
		// Se a Connection é AutoCommit
		if(isClosed()) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed());
		}
		
		// Se a Connection é AutoCommit
		if(isAutoCommit()) {
			
			throw new JXDaoException(I18N.DAO.connectionIsAutoCommit());
		}
		
		// Se já existe uma Transaction aberta
		if(transactionIsOpen()) {
			
			throw new JXDaoException(I18N.DAO.transactionAlreadyOpen());
		}
		
		transactionIsOpen = true;
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see java.io.Closeable#close()
	 */
	@Override
	public void close() throws IOException {
	
		//
		for(final Statement statement : getStatements()) {
			
			try {
				// Tenta fechar o Statement
				if( !statement.isClosed()) {
					
					statement.close();
				}
				
			}catch(final SQLException sqlException) {
				
				throw new IOException(I18N.DAO.notPossibleCloseStatement(), sqlException);
			}
		}
		
		// Limpa o Pool de Statements
		getStatements().clear();
		
		try {
			try {
				//
				connectionOrTransactionIsOpen();
				
			}catch(final SQLException sqlException) {
				
				commit();
				
				getConnection().close();
			}
			
			connection = null;
			
			openConnections--;
			
		}catch(final SQLException sqlException) {
			
			try {
				//
				rollback();
				
			}catch(final SQLException sqlExceptionRollback) {
				
				throw new IOException(sqlExceptionRollback.getMessage(), sqlExceptionRollback.getCause());
			}
			
			throw new IOException(I18N.DAO.notPossibleCloseConnection(), sqlException);
		}
	}
	
	/**
	 * Try to commit the transaction
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
	
		//
		if(transactionIsOpen()) {
			
			transactionIsOpen = false;
			
			//
			for(final Statement statement : getStatements()) {
				
				try {
					// Tenta fechar o Statement
					if( !statement.isClosed()) {
						
						statement.close();
					}
					
				}catch(final SQLException sqlException) {
					
					throw new JXDaoException(I18N.DAO.notPossibleCloseStatement(), sqlException);
				}
			}
			
			// Limpa o Pool de Statements
			getStatements().clear();
			
			// Se a Connection não é AutoCommit
			if( !isAutoCommit()) {
				
				try {
					// Tenta confirmar a Connection
					getConnection().commit();
					getConnection().close();
					
				}catch(final SQLException sqlException) {
					
					throw new JXDaoException(I18N.DAO.notPossibleCommit(), sqlException);
				}
			}
		}
	}
	
	/**
	 * @return statement
	 * @throws JXDaoException
	 */
	public Statement createStatement() throws JXDaoException {
	
		// Tenta criar o Statement
		final Statement statement = createStatementLocal();
		
		getStatements().add(statement);
		
		return statement;
	}
	
	/**
	 * @param resultSetType
	 * @param resultSetConcurrency
	 * @return statement
	 * @throws JXDaoException
	 */
	public Statement createStatement(final int resultSetType, final int resultSetConcurrency) throws JXDaoException {
	
		try {
			// Tenta criar o Statement
			final Statement statement = getConnection().createStatement(resultSetType, resultSetConcurrency);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param resultSetType
	 * @param resultSetConcurrency
	 * @param resultSetHoldability
	 * @return statement
	 * @throws JXDaoException
	 */
	public Statement createStatement(final int resultSetType, final int resultSetConcurrency, final int resultSetHoldability) throws JXDaoException {
	
		try {
			// Tenta criar o Statement
			final Statement statement = getConnection().createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	@Override
	public boolean equals(final Object obj) {
	
		if(this == obj) {
			return true;
		}
		if(obj == null) {
			return false;
		}
		if( !(obj instanceof Conn)) {
			return false;
		}
		final Conn other = (Conn)obj;
		if(hash != other.hash) {
			return false;
		}
		return true;
	}
	
	/**
	 * @param sql
	 *        an SQL statement to be sent to the database, typically a static SQL SELECT statement
	 * @return a ResultSet object that contains the data produced by the given query; never null
	 * @throws SQLException
	 */
	public ResultSet executeQuery(final String sql) throws SQLException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		return createStatementLocal().executeQuery(sql);
	}
	
	/**
	 * @param sql
	 *        an SQL statement to be sent to the database, typically a static SQL SELECT statement
	 * @param maxRows
	 * @return a ResultSet object that contains the data produced by the given query; never null
	 * @throws SQLException
	 */
	public ResultSet executeQuery(final String sql, final int maxRows) throws SQLException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		final Statement statement = createStatementLocal();
		
		//
		if(maxRows > 0) {
			
			statement.setMaxRows(maxRows);
		}
		
		return statement.executeQuery(sql);
	}
	
	/**
	 * @param sql
	 *        an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
	 * @return either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing
	 * @throws SQLException
	 */
	public int executeUpdate(final String sql) throws SQLException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		return createStatementLocal().executeUpdate(sql);
	}
	
	/**
	 * @return connection
	 */
	public Connection getConnection() {
	
		return connection;
	}
	
	/**
	 * @return dbInfo
	 */
	public DBInfo getDBInfo() {
	
		return dbInfo;
	}
	
	/**
	 * @return {@link DBManager}
	 */
	public DBManager getManager() {
	
		return manager;
	}
	
	/**
	 * @return the statements
	 */
	public List<Statement> getStatements() {
	
		return statements;
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#hashCode()
	 */
	@Override
	public int hashCode() {
	
		final int prime = 31;
		int result = 1;
		result = prime * result + (int)(hash ^ hash >>> 32);
		return result;
	}
	
	/**
	 * @return the current state of this <code>Connection</code> object's
	 *         auto-commit mode
	 * @exception SQLException
	 *            if a database access error occurs
	 *            or this method is called on a closed connection
	 */
	public boolean isAutoCommit() throws SQLException {
	
		return getConnection() != null && getConnection().getAutoCommit();
	}
	
	/**
	 * @return <code>true</code> if this <code>Connection</code> object
	 *         is closed; <code>false</code> if it is still open
	 * @exception SQLException
	 *            if a database access error occurs
	 */
	public boolean isClosed() throws SQLException {
	
		return getConnection() == null || getConnection().isClosed();
	}
	
	/**
	 * @return <code>true</code> if this <code>Connection</code> object
	 *         is still open; <code>false</code> if it is closed
	 * @exception SQLException
	 *            if a database access error occurs
	 */
	public boolean isOpen() throws SQLException {
	
		return getConnection() != null && !getConnection().isClosed();
	}
	
	/**
	 * autoCommit is true
	 * 
	 * @return connection
	 * @throws JXDaoException
	 */
	public Connection newConnection() throws JXDaoException {
	
		return newConnection(true);
	}
	
	/**
	 * @param autoCommit
	 * @return connection
	 * @throws JXDaoException
	 */
	public Connection newConnection(final boolean autoCommit) throws JXDaoException {
	
		try {
			//
			connectionOrTransactionIsOpen();
			
			try {
				//
				Class.forName(getDriverName());
				
			}catch(final ClassNotFoundException classNotFoundException) {
				
				throw new JXDaoException(I18N.DAO.driverNotFound(getDriverName()), classNotFoundException);
			}
			
			//
			if(getDBInfo().getDriver().equals(Driver.MYSQL) || getDBInfo().getDriver().equals(Driver.SQLSERVER)) {
				
				connection = DriverManager.getConnection(getDBInfo().getUrl());
				
			}else {
				
				connection = DriverManager.getConnection(getDBInfo().getUrl(), getDBInfo().getUser(), getDBInfo().getPassword());
			}
			
			connection.setAutoCommit(autoCommit);
			
			//
			if( !autoCommit) {
				
				begin();
			}
			
			// Limpa o Pool de Statements
			getStatements().clear();
			
			openConnections++;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleOpenConnection(), sqlException);
		}
		
		return connection;
	}
	
	/**
	 * @return preparedStatement
	 * @throws JXDaoException
	 */
	public PreparedStatement prepareStatement(final String sql) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param sql
	 * @param autoGeneratedKeys
	 * @return preparedStatement
	 * @throws JXDaoException
	 */
	public PreparedStatement prepareStatement(final String sql, final int autoGeneratedKeys) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql, autoGeneratedKeys);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param sql
	 * @param resultSetType
	 * @param resultSetConcurrency
	 * @return preparedStatement
	 * @throws JXDaoException
	 */
	public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql, resultSetType, resultSetConcurrency);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param sql
	 * @param resultSetType
	 * @param resultSetConcurrency
	 * @param resultSetHoldability
	 * @return preparedStatement
	 * @throws SQLException
	 */
	public PreparedStatement prepareStatement(final String sql, final int resultSetType, final int resultSetConcurrency, final int resultSetHoldability) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param sql
	 * @param columnIndexes
	 * @return preparedStatement
	 * @throws JXDaoException
	 */
	public PreparedStatement prepareStatement(final String sql, final int[] columnIndexes) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql, columnIndexes);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @param sql
	 * @param columnNames
	 * @return preparedStatement
	 * @throws JXDaoException
	 */
	public PreparedStatement prepareStatement(final String sql, final String[] columnNames) throws JXDaoException {
	
		ParameterCheck.isNullOrAbsoluteEmpty(sql, "sql");
		ParameterCheck.isNullOrAbsoluteEmpty(columnNames, "columnNames");
		
		showSQL(sql);
		
		try {
			// Tenta criar o PreparedStatement
			final PreparedStatement statement = getConnection().prepareStatement(sql, columnNames);
			
			getStatements().add(statement);
			
			return statement;
			
		}catch(final SQLException sqlException) {
			
			throw new JXDaoException(I18N.DAO.notPossibleCreateStatement(), sqlException);
			
		}catch(final NullPointerException nullPointerException) {
			
			throw new JXDaoException(I18N.DAO.connectionIsClosed(), nullPointerException);
		}
	}
	
	/**
	 * @throws JXDaoException
	 */
	public void rollback() throws JXDaoException {
	
		try {
			// Se a Connection está aberta
			if( !isAutoCommit()) {
				
				// Tenta desfazer as operações
				getConnection().rollback();
			}
			
			transactionIsOpen = false;
			
		}catch(final SQLException sqlExceptionRollback) {
			
			throw new JXDaoException(I18N.DAO.notPossibleRollback(), sqlExceptionRollback);
		}
	}
	
	/**
	 * @param manager
	 */
	public void setManager(final DBManager manager) {
	
		ParameterCheck.isNull(manager, "manager");
		
		this.manager = manager;
	}
	
	/**
	 * @return transactionIsOpen
	 */
	public boolean transactionIsOpen() {
	
		return transactionIsOpen;
	}
	
	/**
	 * @return the openConnections
	 */
	public static int getOpenConnections() {
	
		return openConnections;
	}
	
	/**
	 * @param statement
	 */
	public static void showSQL(final PreparedStatement statement) {
	
		showSQL(statement != null ? statement.toString() : "statement = null");
	}
	
	/**
	 * @param sql
	 */
	public static void showSQL(final String sql) {
	
		//
		if(DBManager.isShowSQL()) {
			
			System.out.println("\njxDao:\n" + hideMD5Value(sql));
		}
	}
	
	/**
	 * @param sql
	 * @return the sql with the value of MD5 hidden
	 */
	private static final String hideMD5Value(final String sql) {
		
		// 
		if(sql.contains("MD5('")) {
			
			int md5IndexStart = sql.indexOf("MD5");
			
			String sqlMD5 = sql.substring(md5IndexStart);
			
			int md5IndexEnd = sqlMD5.indexOf("')") + 2;
			
			sqlMD5 = sqlMD5.substring(0, md5IndexEnd);
			
			// 
			while(sqlMD5.startsWith("MD5(?)")) {
				
				String sql2 = sql.substring((md5IndexStart + 6), sql.length());
				
				md5IndexStart = sql2.indexOf("MD5");
				
				sqlMD5 = sql2.substring(md5IndexStart);
				
				md5IndexEnd = sqlMD5.indexOf("')") + 2;
				
				sqlMD5 = sqlMD5.substring(0, md5IndexEnd);
			}
			
			return hideMD5Value(sql.replace(sqlMD5, "MD5(?)"));
			
		}else {
			
			return sql;
		}
	}
}
